﻿<%@ WebHandler Language="C#" Class="ApsPlanInfoPreAllSetDetailReport" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Collections;
using System.IO;

/**
 * Description: 预排工序生产物资需求计划报表
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2025.1.8
 */

public class ApsPlanInfoPreAllSetDetailReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        getExportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询公用sql
    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT cci.mfgordername,cci.containername,f.factoryname,cci.productname,cci.productdescription,
       wfb.workflowname || ':' || wf.workflowrevision workflowinfo,
       apsd.workflowstepname,s.specno,apsd.starttime,
       tm.toolname,tm.description,tm.qty needqty,tm.tooltype,tm.iscomp,tm.expirationdate
FROM apsplaninfopre aps
LEFT JOIN apsplandetailpre apsd ON apsd.apsplaninfoid = aps.apsplaninfoid
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflow wf ON wf.workflowid = aps.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowstepid = apsd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
INNER JOIN (
    SELECT ws.workflowstepname,ws.workflowstepid,
           t.toolname,t.description,t.tooltype,
           to_char(NVL(t.qty,0)) qty,to_char(NVL(tg.menge,0)) labstall,
           CASE WHEN NVL(t.qty,0) >= NVL(tg.menge,0) THEN to_char(NVL(t.qty,0) - NVL(tg.menge,0)) ELSE '0' END lackqty,
           CASE WHEN NVL(tg.menge,0)>=NVL(t.qty,0) THEN '是' ELSE '否' END iscomp,
           '/' AS expirationdate
    FROM apsplandetailpre apsd
    LEFT JOIN containercurrentinfo cci ON cci.containerid = apsd.containerid
    LEFT JOIN workflowstep ws ON ws.workflowstepid = apsd.workflowstepid
    LEFT JOIN (
       SELECT SUM(qty) qty,ct.toolname,ct.description,drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
       FROM (
          SELECT t.qty, t.sequence,t.tools,t.workflowstepid
          FROM apsplandetailpre apsd
          INNER JOIN containercurrentinfo cci ON cci.containerid = apsd.containerid
          INNER JOIN workflowsteptools t ON t.workflowstepid = apsd.workflowstepid
          WHERE apsd.isuse = 0 ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND apsd.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //需求日期
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            sql += string.Format(" AND apsd.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND apsd.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        sql += @"
       ) t 
       LEFT JOIN (
          SELECT cuttoolname toolname,DESCRIPTION,cuttoolid toolid,resourcetypeid,drawingno,'工装工具' tooltype
          FROM  cuttool
          UNION ALL
          SELECT measuretoolname toolname,DESCRIPTION,measuretoolid toolid,resourcetypeid,drawingno,'工装工具' tooltype 
          FROM  measuretool
          UNION ALL
          SELECT specialtoolname toolname,DESCRIPTION,specialtoolid toolid,resourcetypeid,drawingno,'工装工具' tooltype
          FROM  specialtool
          UNION ALL
          SELECT aux.auxmaterialname toolname,aux.description,aux.auxmaterialid toolid,aux.resourcetypeid,NULL drawingno,'辅材' tooltype
          FROM auxmaterial aux
       ) ct  ON ct.toolid = t.tools
       LEFT JOIN resourcetype rt  ON rt.resourcetypeid = ct.resourcetypeid WHERE ct.toolid IS NOT NULL 
       GROUP BY ct.toolname,ct.description,ct.drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
    ) t ON t.workflowstepid=ws.workflowstepid
    LEFT JOIN (
         SELECT SUM(tg.qty) menge,tg.toolname 
         FROM tool_stock_info tg 
         GROUP BY tg.toolname
    ) tg ON tg.toolname=t.toolname
    WHERE t.toolname IS NOT NULL AND apsd.isuse = 0";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND apsd.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //需求日期
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            sql += string.Format(" AND apsd.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND apsd.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        sql += @"
    UNION ALL 
    SELECT ws.workflowstepname,ws.workflowstepid,     
           sr.specialcoursespecreportno toolname,'/' description,'特殊过程' tooltype,
           '/' qty,'/' labstall,
           '/' lackqty,
           CASE WHEN sr.indate IS NOT NULL AND sr.indate>SYSDATE THEN '是' ELSE '否' END iscomp,
           to_char(sr.indate,'yyyy-MM-dd hh24:mi:ss') AS expirationdate
    FROM apsplandetailpre apsd
    LEFT JOIN containercurrentinfo cci ON cci.containerid = apsd.containerid
    LEFT JOIN workflowstep ws ON ws.workflowstepid = apsd.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN specialcoursespecmapp sc ON sc.workflowstepid = ws.workflowstepid 
    LEFT JOIN specialcoursespecreport sr ON sr.specialcoursespecreportid = sc.specialcoursespecreportid
    WHERE sr.specialcoursespecreportno IS NOT NULL AND NVL(s.isspotcheck,0)=1 AND apsd.isuse = 0";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND apsd.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //需求日期
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            sql += string.Format(" AND apsd.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND apsd.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        sql += @"
    UNION ALL
    SELECT ws.workflowstepname,ws.workflowstepid,     
           '/' toolname,s.nccode description,'数控程序' tooltype,
           '/' qty,'/' labstall,
           '/' lackqty,
           CASE WHEN NVL(s.ncnumber,'0') = '1' AND s.nccode IS NULL THEN '否' ELSE '是' END iscomp,
           '/' AS expirationdate
    FROM apsplandetailpre apsd
    LEFT JOIN containercurrentinfo cci ON cci.containerid = apsd.containerid
    LEFT JOIN workflowstep ws ON ws.workflowstepid = apsd.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    WHERE NVL(s.ncnumber,'0') = '1' AND apsd.isuse = 0";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND apsd.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //需求日期
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            sql += string.Format(" AND apsd.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND apsd.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        sql += @"
) tm ON tm.workflowstepid = ws.workflowstepid
WHERE apsd.isuse = 0";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND apsd.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //类型
        if (jObject["typename"] != null && jObject["typename"].ToString() != "")
        {
            string typename = jObject["typename"].ToString();
            sql += string.Format(" AND tm.tooltype = '{0}'", typename);
        }

        //需求日期
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            sql += string.Format(" AND apsd.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND apsd.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        //是否齐套
        if (jObject["isallset"] != null && jObject["isallset"].ToString() != "")
        {
            sql += string.Format(" AND tm.iscomp = '{0}'", jObject["isallset"].ToString());
        }

        return sql;
    }

    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["workflowinfo"].Caption = "工艺规程";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["specno"].Caption = "工序号";
        dtResult.Columns["workflowstepname"].Caption = "工序名称";
        dtResult.Columns["tooltype"].Caption = "类型";
        dtResult.Columns["toolname"].Caption = "编码";
        dtResult.Columns["description"].Caption = "名称";
        dtResult.Columns["needqty"].Caption = "需求数量";
        dtResult.Columns["expirationdate"].Caption = "有效期";
        dtResult.Columns["starttime"].Caption = "需求日期";
        dtResult.Columns["iscomp"].Caption = "是否齐套";

        ExportHelper.ExportExcel(dtResult, "预排工序生产物资需求计划报表导出数据");
    }
}